from PIL import Image
from PIL import ImageDraw
from PIL import ImageFont
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, OneCellAnchor
from openpyxl.drawing.xdr import XDRPositiveSize2D
from openpyxl.utils.units import pixels_to_EMU

# ------------这个三个函数组合可以插入任何图片到任何位置-----------------------------
def error_image(imageFile, str_number, new_image_path):
    fontstyle = ImageFont.truetype(r'/System/Library/Fonts/Symbol.ttf', 700)
    tp = Image.open(imageFile)
    draw = ImageDraw.Draw(tp)
    draw.text((270, 120), str(str_number), (0, 0, 0), fontstyle)
    tp.save(new_image_path)


def offset_img(img):
    """精确设置图片位置，偏移量以万为单位进行微调吧，具体计算公式太麻烦了
    row column 的索引都是从0开始的,我这里要把图片插入到单元格B10
    """
    p2e = pixels_to_EMU
    h, w = img.height, img.width
    size = XDRPositiveSize2D(p2e(w), p2e(h))
    marker = AnchorMarker(col=13, colOff=60000, row=8, rowOff=0)
    img.anchor = OneCellAnchor(_from=marker, ext=size)


def save_to_excel(file, fig):
    """把图片插入到excel文件中的指定位置
    插入到指定单元格然后向右偏移一点距离，目的是不让图片的边框和单元格的边框重合
    :param file: excel文件
    :param fig: 图片
    :return:
    """
    print('开始')
    # 获取模板 excel 的操作对象
    wb = load_workbook(filename=file)
    # 根据 Sheet1 这个名字来获取 Sheet1
    sheet1 = wb['第1页']
    # sheet1 = wb[subject]
    img = Image(fig)
    # 设置图片的宽和高
    cnt = 81
    img.width, img.height = (5 * cnt, 5 * cnt)
    offset_img(img)
    # 将图片添加到excel中
    sheet1.add_image(img)

    wb.save('/Users/mac/Desktop/123456.xlsx')

# ------------这个三个函数组合可以插入任何图片到任何位置-----------------------------